﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BoutiquePOS.DAL.DAO;


namespace BoutiquePOS.DAL.Gateway
{
    public class SalesReturnDtlsGateway : DbGateway
    {
        public string SaveSalesReturn(SalesReturn aSalesReturn)
        {
            try
            {
                ConnectionObj.Open();
                string query = String.Format("INSERT INTO PosSalesReturn VALUES(@salesId,@salesReturnDate,@totalNetAmount)");
                CommandObj.CommandText = query;
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@salesId", SqlDbType.Int);
                CommandObj.Parameters["@salesId"].Value = aSalesReturn.SalesId;
                CommandObj.Parameters.Add("@salesReturnDate", SqlDbType.VarChar);
                CommandObj.Parameters["@salesReturnDate"].Value = aSalesReturn.SalesReturnDate;
                CommandObj.Parameters.Add("@totalNetAmount", SqlDbType.Decimal);
                CommandObj.Parameters["@totalNetAmount"].Value = aSalesReturn.TotalNetAmount;
                CommandObj.ExecuteNonQuery();
                ConnectionObj.Close();     
            }
            catch (Exception ex)
            {
                throw new Exception("Error in data");
            }
            return "Save success";
        }

        public string SaveSalesReturnDtls(int Id,List<SalesReturnDtls>aSalesReturnDtls1)
         {
             foreach (SalesReturnDtls aSalesReturnDtls in aSalesReturnDtls1)
             {
                 try
                 {
                     ConnectionObj.Open();
                     string query1 = String.Format("INSERT INTO PosSalesReturnDtls VALUES(@salesReturnId,@salesId,@productId,@returnQty,@returnFreeQty)");
                     CommandObj.CommandText = query1;
                     CommandObj.Parameters.Clear();
                     CommandObj.Parameters.Add("@salesReturnId", SqlDbType.Int);
                     CommandObj.Parameters["@salesReturnId"].Value =Id;
                     CommandObj.Parameters.Add("@salesId", SqlDbType.Int);
                     CommandObj.Parameters["@salesId"].Value =aSalesReturnDtls.SalesId;
                     CommandObj.Parameters.Add("@productId", SqlDbType.Int);
                     CommandObj.Parameters["@productId"].Value = aSalesReturnDtls.ProductId;
                     CommandObj.Parameters.Add("@returnQty", SqlDbType.Int);
                     CommandObj.Parameters["@returnQty"].Value = aSalesReturnDtls.ReturnQty;
                     CommandObj.Parameters.Add("@returnFreeQty", SqlDbType.Int);
                     CommandObj.Parameters["@returnFreeQty"].Value = aSalesReturnDtls.ReturnFreeQty;
                     CommandObj.ExecuteNonQuery();
                     ConnectionObj.Close();
                 }
                 catch (Exception ex)
                 {
                     throw new Exception("Error in data");
                 }
             }      
             return "Save success1";
         }
        
        public string GetProductNameAutoCompleteData(int formId)
        {
            string suggestionList = "";
            try
            {
                string queryString = string.Format("SELECT productBrandName  FROM  PosProduct Where formId={0}", formId);

                CommandObj.Connection = ConnectionObj;
                CommandObj.CommandText = queryString;
                ConnectionObj.Open();
                SqlDataReader reader = CommandObj.ExecuteReader();

                while (reader.Read())
                {
                    if (string.IsNullOrEmpty(suggestionList))
                    {
                        suggestionList += "\"" + reader["productBrandName"].ToString() + "\"";
                    }
                    else
                    {
                        suggestionList += ", \"" + reader["productBrandName"].ToString() + "\"";
                    }
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Couldn't Loaded Data", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return suggestionList;
        }

        //SalesId Return//
        public int ReturnSalesId(string salesNo)
        {
            int id = 0;
            try
            {
                string search = String.Format("Select salesId FROM PosSales WHERE salesNo='{0}'", salesNo);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    id = Convert.ToInt32(reader[0]);
                }
                return id;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not found data", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }

        internal int SalesReturnId(int salesReturnId)
        {
            int id1 = 0;
            try
            {
                string search = String.Format("Select * FROM PosSalesReturn WHERE SalesId='{0}'", salesReturnId);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    id1 = Convert.ToInt32(reader[0]);
                }
                return id1;
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found SalesDtls", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
        }
        public List<SalesDtls>GetAllSalesProduct(int salesId)
        {
           List<SalesDtls>salesDtlses=new List<SalesDtls>();
            try
            {
                string search = String.Format("SELECT PharSalesDtls.*, PharProduct.productBrandName FROM  PharSalesDtls INNER JOIN PharProduct ON PharSalesDtls.productId = PharProduct.productId and PharSalesDtls.salesId='{0}'", salesId);
                ConnectionObj.Open();
                CommandObj.CommandText = search;
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    SalesDtls salesDtls=new SalesDtls();
                    Product product=new Product();
                    salesDtls.AProduct = product;
                    salesDtls.SalesId =Convert.ToInt32(reader[0]);
                    salesDtls.AProduct.ProductId = Convert.ToInt32(reader[2]);
                    salesDtls.AProduct.ProductBrandName = reader[10].ToString();
                    salesDtls.SalesQty = Convert.ToInt32(reader[3]);
                    salesDtls.TradePrice =Convert.ToDecimal(reader[4]);
                    salesDtls.Mrp =Convert.ToDecimal(reader[5]);
                    salesDtls.TotalMrp =Convert.ToDecimal(reader[6]);
                    salesDtls.DiscountPer =Convert.ToDouble(reader[7]);
                    salesDtls.DiscountAmt =Convert.ToDecimal(reader[8]);
                    salesDtls.SalesFreeQty=Convert.ToInt32(reader[9]);
                    
                    salesDtls.ReturnQty = 0;
                    salesDtls.ReturnFreeQty = 0;
                    salesDtlses.Add(salesDtls);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("Could not Found SalesDtls", exception);
            }
            finally
            {
                if (ConnectionObj != null && ConnectionObj.State == ConnectionState.Open)
                {
                    ConnectionObj.Close();
                }
            }
            return salesDtlses;

        }

    }
}